<?php

/**
 * Welcome page
 *
 * @package BABEL
 * @since   BABEL 0.1.0
 * @version $Id: welcome.php 766 2008-05-14 08:52:59Z bjori $
 * @copyright Copyright (C) 2008 Frank Bølviken
 * @copyright COpyright (C) 2008 Magnus Bakke
 * @author  Frank Bølviken
 * @author 	Magnus Bakke
 * @license LGPL
 */


$obj_tpl->setFile("babel_welcome","babel/welcome.html");

/* {{{ News section /*

/*
 * SQL statement which returns the news from the DB
 */
$DB_QUERY = "
			SELECT
				n.title, 
				n.body,
				n.time_created,
				u.username	
			FROM
				babel_news n,
				babel_users u
			WHERE
				n.user_id = u.user_id
			ORDER BY
				n.time_created DESC
			";
if (!PDODB::runQuery($DB_QUERY, null, $stmt)) {
	$str = var_export(PDODB::connect()->errorInfo(), true);
	throw new Exception("Query#1 failed" . $str);
}

$obj_tpl->setBlock("babel_welcome", "news_list", "news_list_ref");

/*
 * Loops through the array, and prints the news in the template.
 */
foreach($stmt as $row) {
	$obj_tpl->setVar(array(
							"VAR_TITLE"		=>$row["title"],
							"VAR_BODY"		=>$row["body"],
							"VAR_TIME"		=>$row["time_created"],
							"VAR_USER"		=>$row["username"]
					      )
				     );
	$obj_tpl->parse("news_list_ref", "news_list", true);
}
/* }}} */

/* {{{ Language progress section */

$obj_tpl->setBlock("babel_welcome", "language_progress", "prog_ref");

/*
 * SQL statement which counts and returns how many constants there is in the DB
 */
$DB_QUERY = "
SELECT
	COUNT(original_id)
FROM 
	babel_originals
";

$row = PDODB::execQueryFetch($DB_QUERY, PDO::FETCH_COLUMN);
$nr_originals = $row[0];

$DB_QUERY = "
SELECT
	language_id AS id,
	name
FROM
	babel_languages
";
if (!PDODB::runQuery($DB_QUERY, null, $languages)) {
	throw new Exception("Query failed");
}

/*
 * SQL statement which returns how many translations on a language has the 
 * status FINAL
 *
 * 1 param:
 * BABEL_TRANSLATION_FINAL 	:	The number which defines that the status is 
 * 								FINAL
 * 2 param:
 *    Language ID
 */
$DB_QUERY = "
SELECT
	COUNT(t.value_id) as number
FROM
	babel_translations t
WHERE
	t.status = ? AND t.language_id = ?
";
$status = PDODB::connect()->prepare($DB_QUERY);

/*
 * Loops through the languages and prints the data in the template.
 */
$nr = 1;
foreach ($languages as $lang) {
	$status->execute(array(BABEL_TRANSLATION_FINAL, $lang["id"]));
	$row = $status->fetch(PDO::FETCH_ASSOC);
	$obj_tpl->setVar(array(
						"LANG_NAME"		=>  $lang["name"],
						"ELEMENT"		=>	"element" . $nr++,
						"PERCENT_COMP"	=>	($row["number"] / $nr_originals) * 100
						)
				);
	$obj_tpl->parse("prog_ref", "language_progress", true);
}
/* }}} /*

/* {{{ Hall of Fame section */

$obj_tpl->setBlock("babel_welcome", "hof_list", "hof_list_ref");

/*
 * SQL statement which returns the total experience points a user has gathered.
 */
$DB_QUERY = "
			SELECT
				username AS username,
			SUM(lang_exp) AS lang_exp
			FROM
				babel_users u,
				babel_user_skills us
			WHERE 
				u.user_id = us.user_id
			GROUP BY username
			ORDER BY lang_exp
 			DESC
			LIMIT 10
			";

if (!PDODB::runQuery($DB_QUERY, null, $stmt)) {
	throw new Exception("Query#2 failed");
}

/*
 * Loops through the array and prints out the data in the template.
 */
$rowCount = 0;
foreach($stmt as $row) {
	$obj_tpl->setVar(array(
						   "ROW_TR_BGCOLOR"			=> $rowCount % 2 ? '#EEEEEE' : '#DDDDDD',
						   "VAR_LIST_POSITION"		=> ++$rowCount,
						   "VAR_NAME"				=> $row["username"],
						   "VAR_RANK"				=> $row["lang_exp"]
						   )
					);
	$obj_tpl->parse("hof_list_ref", "hof_list", true);
}
/* }}} */

$obj_tpl->parse("babel_welcome","babel_welcome");
$obj_tpl->p("babel_welcome");

